package com.ggb;

import java.sql.*;
import java.util.Scanner;
import java.util.UUID;

public class Demo {
    private static  final  String DRIVER = "com.mysql.cj.jdbc.Driver";
    // private static  final  String URL = "jdbc:mysql://localhost:3306/rbac?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static final String URL = "jdbc:mysql://localhost:3306/rbac?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "12345678";
    private static final Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Q10UpdatePower();
    }

    private static void Q10UpdatePower() throws ClassNotFoundException, SQLException {
        // 显示所有的映射关系
        String sql = "select * from t_user u JOIN t_role r USING(rid) JOIN t_role_power USING(rid) JOIN t_power p USING(pid)";
        // JDBC查询
        ResultSet resultSet = jdbcDQL(sql);
        while(resultSet.next()){
            System.out.println("用户【"+resultSet.getString("u.username")+"】的角色是【"+resultSet.getString("r.rolename")+"】角色id是【"+resultSet.getString("rid")+"】  权限是【"+resultSet.getString("p.powername")+"】权限id是【"+resultSet.getString("pid")+"】");
        }

        // 📱信息
        System.out.println("请输入你要操作的角色ID");
        String rid = scanner.next();
        System.out.println("请输入你要操作的权限名【新增，删除】");
        String operation = scanner.next();
        System.out.println("请输入你要操作的权限ID");
        String pid = scanner.next();
        String sqlOper = "";
        String rpid = UUID.randomUUID().toString();
        if ("新增".equals(operation)){
            sqlOper = "insert into t_role_power values('"+rpid+"','"+rid+"','" + pid + "')";
        }else if("删除".equals(operation)){
            sqlOper = "delete from t_role_power where rid = '" + rid + "' and pid = '" + pid + "'";
        }
        System.out.println(sqlOper);
        // JDBC
        int rows = jdbcDML(sqlOper);
        System.out.println(rows == 1 ? "操作成功" : "操作失败");
    }

    private static void Q09SelectPowerUser() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您要查看的权限名");
        String powerName= scanner.next();
        //  sql SELECT * FROM t_user u JOIN t_role r USING(rid) JOIN t_role_power USING(rid) JOIN t_power p USING(pid) WHERE powername = "修改"
        String sql = "SELECT * FROM t_user u JOIN t_role r USING(rid) JOIN t_role_power USING(rid) JOIN t_power p USING(pid) WHERE powername = '" + powerName + "'";
        // JDBC查询
        ResultSet resultSet = jdbcDQL(sql);
        while(resultSet.next()){
            System.out.println("用户【"+resultSet.getString("u.username")+"】的角色是【"+resultSet.getString("r.rolename")+"】权限是【"+resultSet.getString("p.powername")+"】");
        }
    }

    /**
     * 查询用户角色权限
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static void Q08UserPower() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username= scanner.next();
        System.out.println("请输入您的密码");
        String password= scanner.next();
        boolean isLogin = false;
        // sql  SELECT * from t_user u JOIN t_role r USING(rid) JOIN t_role_power USING(rid) JOIN t_power p USING(pid) WHERE username = 'ggb' and password = '123455';
        String sql = "SELECT * from t_user u JOIN t_role r USING(rid) JOIN t_role_power USING(rid) JOIN t_power p USING(pid) WHERE username = '" + username + "' and password = '" + password + "'";
        // JDBC查询

        ResultSet resultSet = jdbcDQL(sql);
        while(resultSet.next()){
            isLogin = true;
            System.out.println("用户【"+username+"】的角色是【"+resultSet.getString("r.rolename")+"】权限是【"+resultSet.getString("p.powername")+"】");
        }
        if (!isLogin){
            System.out.println("用户【"+username+"】密码【"+password+"】登录失败");
        }
    }

    /**
     * 更新用户角色
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static void Q07UpdateRole() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username= scanner.next();
        // sql SELECT r.rolename from t_user u JOIN t_role r USING(rid) WHERE username="ggb";
        String sql = "SELECT r.rolename from t_user u JOIN t_role r USING(rid) WHERE username='" + username + "'";
        // JDBC查询
        ResultSet resultSet = jdbcDQL(sql);
        if (resultSet.next()) {
            System.out.println("用户【"+username+"】登录成功");
            System.out.println("您的角色是【"+resultSet.getString("r.rolename")+"】");
            // 显示所有角色信息
            String roleSql = "select * from t_role";
            ResultSet resultSetRole = jdbcDQL(roleSql);
            while (resultSetRole.next()) {
                System.out.println("角色ID【"+resultSetRole.getString("rid")+"】角色名称【"+resultSetRole.getString("rolename")+"】");
            }
            System.out.println("请输入您的角色ID");
            String rid= scanner.next();
            // String roleName= scanner.next();
            // 修改用户的角色
            String updateSql = "update t_user set rid = '" + rid + "' where username = '" + username + "'";
            // String updateSql1 = "update t_user set rid = (select rid from t_role  where rolename = '" + roleName + "') where username = '" + username + "'";
            // JDBC
            int rows = jdbcDML(updateSql);
            System.out.println(rows == 1 ? "用户【"+username+"】更新角色成功" : "【"+username+"】更新角色失败");
        } else {
            System.out.println("用户【"+username+"】查无此人");
        }
    }

    private static void InsertUsers() throws ClassNotFoundException, SQLException {
        // 循环插入
        for(int i=0;i<50;i++){
            String uid = UUID.randomUUID().toString();
            String username = "user"+i;
            double password = Math.random()*900000 + 100000;
            String phoneNumber = "1234567890";
            String rid = "2";
            String sql = "insert into t_user values('" + uid + "', '" + rid + "', '" + username + "', '" + password + "', '" + phoneNumber + "', '2020-12-12 12:12:12')";
            int rows = jdbcDML(sql);
            System.out.println(rows == 1 ? "插入成功" : "插入失败");
        }
    }

    /**
     * 用户登录
     * @throws ClassNotFoundException
     * @throws SQLException
     */

    private static void UserLogin() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username= scanner.next();
        System.out.println("请输入您的密码");
        String password= scanner.next();
        // sql
        String sql = "select * from t_user where username = '" + username + "' and password = '" + password + "'";
        // JDBC查询
        ResultSet resultSet = jdbcDQL(sql);
        if (resultSet.next()) {
            System.out.println("用户【"+username+"】密码【"+password+"】登录成功");
        } else {
            System.out.println("用户【"+username+"】密码【"+password+"】登录失败");
        }
    }


    /**
     * 更新用户名
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static void Q4UpdatePassword() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username= scanner.next();
        System.out.println("请输入您的密码");
        String oldPassword= scanner.next();
        System.out.println("请输入您的新密码");
        String password= scanner.next();
        // sql
        String sql = "update t_user set password = '" + password + "' where username = '" + username + "' and password = '" + oldPassword + "'";
        // JDBC修改
        int rows = jdbcDML(sql);
        System.out.println(rows == 1 ? "用户【"+username+"】更新密码成功" : "【"+username+"】更新密码失败");
    }

    /**
     * 删除用户
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static void Q03DeleteUser() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username= scanner.next();
        // sql
        String sql = "delete from t_user where username = '" + username + "'";
        // JDBC删除
        int rows = jdbcDML(sql);
        System.out.println(username + (rows == 1 ? "登录成功" : "登录失败"));
    }
    /**
     * 添加用户
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static void Q02InsertUser() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username = scanner.next();
        System.out.println("请输入您的密码");
        String password = scanner.next();
        System.out.println("请输入您的手机号");
        String phoneNumber = scanner.next();
        String uid = UUID.randomUUID().toString();
        String rid = "1";

        // 2. 插入用户
        String sql = "insert into t_user(uid, username, password, phonenumber, rid) values('" + uid + "', '" + username + "', '" + password + "', '" + phoneNumber + "', '" + rid + "')";
        int rows = jdbcDML(sql);
        System.out.println(rows == 1 ? "插入成功" : "插入失败");
    }

    // 1. 验证用户名是否存在
    private static void Q01ValidateUsernameOnly() throws ClassNotFoundException, SQLException {
        System.out.println("请输入您的用户名");
        String username = scanner.next();

        // 查询数据库中存不存在
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "select * from t_user where username = '" + username + "'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            System.out.println("您的用户名已经存在");
        } else {
            System.out.println("您的用户名不存在");
        }
        // 关闭数据库连接
        resultSet.close();
        statement.close();
        connection.close();
    }

    // JDBC DML
    private static int jdbcDML(String sql) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        Statement statement = connection.createStatement();
        int rows = statement.executeUpdate(sql);
        return rows;
    }

    private static ResultSet jdbcDQL(String sql) throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        Statement statement = connection.createStatement();
        return statement.executeQuery(sql);
    }
}
